package DBconnection.unittests;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

import junit.framework.Assert;

import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

import DBconnection.mysqlConnection;

public class mysqlConnectionTest {
	
	 static mysqlConnection Mycon=null;
	 
	 	int grade1a110Logic=93;
		int grade1a111Logic=64;
		int grade1a110Algebra1=82;
		int grade1a111Algebra1=70;
		
		int grade1b112Logic=89;
		int grade1b113Logic=91;
		int grade1b112Algebra1=98;
		int grade1b113Algebra1=78;
	
	public mysqlConnectionTest()
	{
		try {
			Mycon=new mysqlConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@BeforeClass
	public static void setUpBeforeClass() throws Exception {
		
		if (Mycon==null)
			Mycon=new mysqlConnection();
		PreparedStatement ps=null;
		/*********************create new class***********************/
		String[] msg=new String[2];
		msg[0]="CreateNewClass";
		msg[1]="1";
		Object temp=null;
		temp=Mycon.CreateNewClass(msg);//create new class 1a
		while(temp==null);
		msg[0]="CreateNewClass";
		msg[1]="1";
		Mycon.CreateNewClass(msg);//create new class 1b
		/*********************create new class***********************/
		
		int grade1a110Logic=93;
		int grade1a111Logic=64;
		int grade1a110Algebra1=82;
		int grade1a111Algebra1=70;
		
		int grade1b112Logic=89;
		int grade1b113Logic=91;
		int grade1b112Algebra1=98;
		int grade1b113Algebra1=78;
		
		
		
		try {
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentdb (sid,FirstName,LastName,ClassID)VALUES(110,?,?,?);");
			ps.setString(1,"Yael");
			ps.setString(2,"Barzohar");
			ps.setString(3,"1a");
			
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentdb (sid,FirstName,LastName,ClassID)VALUES(111,?,?,?);");
			ps.setString(1,"Miri");
			ps.setString(2,"Buhadana");
			ps.setString(3,"1a");
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentdb (sid,FirstName,LastName,ClassID)VALUES(112,?,?,?);");
			ps.setString(1,"Dana");
			ps.setString(2,"Spector");
			ps.setString(3,"1b");
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentdb (sid,FirstName,LastName,ClassID)VALUES(113,?,?,?);");
			ps.setString(1,"Ran");
			ps.setString(2,"Sarig");
			ps.setString(3,"1b");
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentcourses VALUES(110,?,?,2011,?,?,?);");
			ps.setString(1,"02101");
			ps.setInt(2, grade1a110Algebra1 );
			ps.setString(3,"B");
			ps.setString(4,"Algebra1");
			ps.setBoolean(5, false);
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentcourses VALUES(111,?,?,2011,?,?,?);");
			ps.setString(1,"02101");
			ps.setInt(2, grade1a111Algebra1 );
			ps.setString(3,"B");
			ps.setString(4,"Algebra1");
			ps.setBoolean(5, false);
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentcourses VALUES(112,?,?,2011,?,?,?);");
			ps.setString(1,"02101");
			ps.setInt(2, grade1b112Algebra1 );
			ps.setString(3,"B");
			ps.setString(4,"Algebra1");
			ps.setBoolean(5, false);
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentcourses VALUES(113,?,?,2011,?,?,?);");
			ps.setString(1,"02101");
			ps.setInt(2, grade1b113Algebra1 );
			ps.setString(3,"B");
			ps.setString(4,"Algebra1");
			ps.setBoolean(5, false);
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentcourses VALUES(110,?,?,2011,?,?,?);");
			ps.setString(1,"02120");
			ps.setInt(2, grade1a110Logic );
			ps.setString(3,"B");
			ps.setString(4,"Logic");
			ps.setBoolean(5, false);
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentcourses VALUES(111,?,?,2011,?,?,?);");
			ps.setString(1,"02120");
			ps.setInt(2, grade1a111Logic );
			ps.setString(3,"B");
			ps.setString(4,"Logic");
			ps.setBoolean(5, false);
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentcourses VALUES(112,?,?,2011,?,?,?);");
			ps.setString(1,"02120");
			ps.setInt(2, grade1b112Logic );
			ps.setString(3,"B");
			ps.setString(4,"Logic");
			ps.setBoolean(5, false);
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.studentcourses VALUES(113,?,?,2011,?,?,?);");
			ps.setString(1,"02120");
			ps.setInt(2, grade1b113Logic );
			ps.setString(3,"B");
			ps.setString(4,"Logic");
			ps.setBoolean(5, false);
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.classescourses VALUES(?,?,120,2011,?);");
			ps.setString(1,"1a");
			ps.setString(2,"02120");
			ps.setString(3,"B");
			
			
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.classescourses VALUES(?,?,121,2011,?);");
			ps.setString(1,"1a");
			ps.setString(2,"02101");
			ps.setString(3,"B");
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.classescourses VALUES(?,?,120,2011,?);");
			ps.setString(1,"1b");
			ps.setString(2,"02120");
			ps.setString(3,"B");
			ps.executeUpdate();
			
			ps = Mycon.getCon().prepareStatement("INSERT INTO test.classescourses VALUES(?,?,121,2011,?);");
			ps.setString(1,"1b");
			ps.setString(2,"02101");
			ps.setString(3,"B");
			ps.executeUpdate();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}

	@AfterClass
	public static void tearDownAfterClass() throws Exception {
		if (Mycon==null)
			Mycon=new mysqlConnection();
		PreparedStatement ps=null;
		ps = Mycon.getCon().prepareStatement("DELETE FROM test.classes WHERE ClassID=? OR ClassID=?;");
		ps.setString(1, "1a");
		ps.setString(2, "1b");
		ps.executeUpdate();
		
		ps = Mycon.getCon().prepareStatement("DELETE FROM test.classesgrades WHERE Grade=1;");
		ps.executeUpdate();
		
		ps = Mycon.getCon().prepareStatement("DELETE FROM test.classescourses WHERE ClassID=? OR ClassID=?;");
		ps.setString(1, "1a");
		ps.setString(2, "1b");
		ps.executeUpdate();
		
		
		ps = Mycon.getCon().prepareStatement("DELETE FROM test.studentcourses WHERE Sid=110 OR Sid=111 OR Sid=112 OR Sid=113;");
		ps.executeUpdate();
		
		
		ps = Mycon.getCon().prepareStatement("DELETE FROM test.studentdb WHERE sid=110 OR sid=111 OR sid=112 OR sid=113;");
		ps.executeUpdate();
		
		
	}

	@Before
	public void setUp() throws Exception {
		
		
	}

	@After
	public void tearDown() throws Exception {
	}

	@Test
	public void testDataForHistBetweenTeachersInChosenClass() {
		String ClassId="1a";
		Integer testedAvgGradeFor120=0;
		Integer testedAvgGradeFor121=0;
		Double Temp= new Double((grade1a110Algebra1+grade1a111Algebra1)/2);
		Integer realAvgGradeFor121=Temp.intValue();
		Temp= new Double((grade1a110Logic+grade1a111Logic)/2);
		Integer realAvgGradeFor120=Temp.intValue();
		
		Vector<String[]> result=new Vector<String[]>();
		String[] msg=new String[2];
		msg[0]="DataForHistBetweenTeachersInChosenClass";
		msg[1]=ClassId;
		result=(Vector)Mycon.DataForHistBetweenTeachersInChosenClass(msg);
		for(int i=0;i<result.size()-1;i++)
		{
			String[] array=result.get(i);
			if (array[0].startsWith("Miriam"))
			{
				Temp=Double.parseDouble(array[2]);
				testedAvgGradeFor120=Temp.intValue();
			}
			else 
				{
				Temp=Double.parseDouble(array[2]);
				testedAvgGradeFor121=Temp.intValue();
				
				}
			
		}
		
		Assert.assertEquals(realAvgGradeFor121, testedAvgGradeFor121);
		Assert.assertEquals(realAvgGradeFor120, testedAvgGradeFor120);
		
		
	}

	@Test
	public void testDataForHistBetweenClassesToChosenTeacher() {
		String TeacherId="120";
		String[] msg=new String[2];
		msg[0]="testDataForHistBetweenClassesToChosenTeacher";
		msg[1]=TeacherId;
		Integer testedAvgGradeFor1a=0;
		Integer testedAvgGradeFor1b=0;
		Double Temp= new Double((grade1a110Logic+grade1a111Logic)/2);
		Integer realAvgGradeFor1a=Temp.intValue();
		Temp= new Double((grade1b112Logic+grade1b113Logic)/2);
		Integer realAvgGradeFor1b=Temp.intValue();
		
		Vector<String[]> result=new Vector<String[]>();
		result=(Vector)Mycon.DataForHistBetweenClassesToChosenTeacher(msg);
		
		for(int i=0;i<result.size()-1;i++)
		{
			String[] array=result.get(i);
			if (array[0].startsWith("1a"))
			{
				Temp=Double.parseDouble(array[1]);
				testedAvgGradeFor1a=Temp.intValue();
			}
			else if (array[0].startsWith("1b"))
				{
				Temp=Double.parseDouble(array[1]);
				testedAvgGradeFor1b=Temp.intValue();
				
				}
			
		}
		
		Assert.assertEquals(realAvgGradeFor1a, testedAvgGradeFor1a);
		Assert.assertEquals(realAvgGradeFor1b, testedAvgGradeFor1b);
		
		
		
		
	}

	@Test
	public void testDataForHistBetweenCoursesToChosenClass() {
		String ClassId="1b";
		Integer testedAvgGradeForLogic=0;
		Integer testedAvgGradeForAlgebra1=0;
		String[] msg=new String[2];
		msg[0]="testDataForHistBetweenCoursesToChosenClass";
		msg[1]=ClassId;
		
		Double Temp= new Double((grade1b112Logic+grade1b113Logic)/2);
		Integer realAvgGradeForLogic=Temp.intValue();
		Temp= new Double((grade1b112Algebra1+grade1b113Algebra1)/2);
		Integer realAvgGradeForAlgebra1=Temp.intValue();
		Vector<String[]> result=new Vector<String[]>();
		result=(Vector)Mycon.DataForHistBetweenCoursesToChosenClass(msg);
		
		for(int i=0;i<result.size()-1;i++)
		{
			String[] array=result.get(i);
			if (array[0].startsWith("Logic"))
			{
				Temp=Double.parseDouble(array[1]);
				testedAvgGradeForLogic=Temp.intValue();
			}
			else if (array[0].startsWith("Algebra1"))
				{
				Temp=Double.parseDouble(array[1]);
				testedAvgGradeForAlgebra1=Temp.intValue();
				
				}
			
		}
		
		Assert.assertEquals(realAvgGradeForLogic, testedAvgGradeForLogic);
		Assert.assertEquals(realAvgGradeForAlgebra1, testedAvgGradeForAlgebra1);
		
		
		
		
	}

}
